# -*- coding: utf-8 -*
import sqlite3
import time
from datetime import datetime
from flask import render_template, request
from flask_paginate import Pagination, get_page_parameter
import numpy as np
from app import app
import db

@app.route("/chart", methods=['GET'])
def chart():
    # 查询当月内的最高、最低温度和湿度
    sql = '''
        select min(temperature) as minTemperature,min(humidity) as minHumidity,
        max(temperature) as maxTemperature,max(humidity) as maxHumidity,
        strftime('%Y-%m-%d',createTime) as createTime from test 
        where date('now','start of month')<strftime('%Y-%m-%d',createTime)
        group by strftime('%Y-%m-%d',createTime)
        '''
    rows = db.query_db(sql)
    minTemperatures = []
    minHumiditys= []
    maxTemperatures = []
    maxHumiditys = []
    createTimes = []
    for row in rows:
        minTemperatures.append(row[0])
        minHumiditys.append(row[1])
        maxTemperatures.append(row[2])
        maxHumiditys.append(row[3])
        createTime = row[4]
        print createTime
        createTimes.append(createTime)
    
    print createTimes
    createTime = tuple(createTimes)
    context = {
        "minTemperatures": minTemperatures,
        "minHumiditys": minHumiditys,
        "maxTemperatures": maxTemperatures,
        "maxHumiditys": maxHumiditys,
        "createTimes": createTime
    }
    return render_template('chart.html', context=context)
    

@app.route("/index", methods=['GET','POST'])
def index():
    per_page = 30
    
    sql = "select * from test where 1=1 "
    if request.method == 'POST':
        startTime = request.form['startTime']
        print startTime
        if startTime != '':
            startTimeSql = " and createTime > '"+startTime+"'"
            sql = sql + startTimeSql
            print sql

        if request.form['endTime'] != '':
            endTime = request.form['endTime']
            endTimeSql = " and createTime <= '"+endTime+"'"
            sql = sql + endTimeSql
    
    sql= sql+" order by createTime desc"
    todos = db.query_db(sql)
    total = len(todos)  # 查询全部条数
    page = request.args.get(get_page_parameter(), type=int, default=1)  # 获取页面，默认第一页
    start = (page-1) * per_page  # 每一页开始位置
    end = start + per_page  # 每一页结束位置
    pagination = Pagination(bs_version=3, total=total, page=page)
    todoList = todos[start:end]
    return render_template('index.html', todos=todoList,pagination=pagination,)

@app.errorhandler(404)
def not_found(error):
    return render_template('404.html')
